﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System;
using System.Collections.Generic;
using System.Text;
using System.Text.RegularExpressions;
using CommonLib.Extensions.Collections;
namespace GraphQlSql
{
    public class Op
    {


        /// <summary>
        /// 对象转sql字符串
        /// </summary>
        /// <param name="model"></param>
        /// <param name="errorMsg"></param>
        /// <param name="dbtype"></param>
        /// <returns></returns>

        public static string GraphQlToSqlString(GraphQlSqlModel model, ref List<string> errorLt, string dbtype = "sqlserver")
        {
            
            StringBuilder sb = new StringBuilder();
            StringBuilder sbtemp = new StringBuilder();
            SqlResult sqlResult = new SqlResult();
            errorLt = new List<string>();
            if (string.IsNullOrEmpty(model.MainTableName) || !ValidatedTableName(model.MainTableName) || !(model.SelectFields != null && model.SelectFields.Count > 0))
            {
                errorLt.Add("主表不可为空，并且返回字段不可为空。");

            }

            sb.Append("select  ");

            #region  获取select的字段
            foreach (var m in model.SelectFields)
            {

                foreach (var f in m.Fields)
                {
                    if (ValidatedTableName(m.TableName) && ValidatedColumnName(m.TableName, f.FieldName)) //字段名是否存在 切不为空
                    {
                        if (string.IsNullOrEmpty(f.AliasName))
                        {
                            sbtemp.Append("  " + m.TableName + "." + f.FieldName + ",");
                        }
                        else
                        {
                            if (IsCnEnNumber(f.AliasName))
                            {
                                sbtemp.Append("  " + m.TableName + "." + f.FieldName + " as  " + f.AliasName + " ,");

                            }
                            else
                            {
                                errorLt.Add(" 字段别名错误"+ f.AliasName + "。");
                            }
                           
                        }
                        

                    }


                }


            }
            string selFields = sbtemp.ToString();

            selFields = selFields.Substring(0, selFields.LastIndexOf(','));
            sbtemp.Length = 0;
            if (string.IsNullOrEmpty(selFields))
            {
                errorLt.Add("必须有要返回的字段。");

            }
            sb.Append(selFields);
            #endregion

            sb.Append("  from " + model.MainTableName);

            #region  表关联
            if (model.TableRefs != null && model.TableRefs.Count > 0)
            {
                foreach (var tr in model.TableRefs)
                {
                    sqlResult = ChinaToEnglist(tr.LinkType);
                    if (sqlResult.IsOk)
                    {
                        sb.Append(sqlResult.SqlString);

                    }
                    else
                    {

                        errorLt.Add("表关联条件不可为空。");

                    }

                    if (!ValidatedTableName(tr.LinkTableName))
                    {
                        errorLt.Add("表关联 表名"+ tr.LinkTableName + "不可为空且必须存在。");
                    }
                    else
                    {
                        sb.Append(tr.LinkTableName);
                    }

                    if (tr.OnConditions != null && tr.OnConditions.Count > 0)
                    {
                        sb.Append("   on  ");
                        List<string> onConditionsLt = new List<string>();

                        foreach (var c in tr.OnConditions)
                        {
                            if (ValidatedTableName(c.LeftTableName) && ValidatedColumnName(c.LeftTableName, c.LeftTableField) && ValidatedColumnName(c.RightTableName, c.RightTableField) && ValidatedTableName(c.RightTableName))
                            {
                                onConditionsLt.Add("   " + c.LeftTableName + "." + c.LeftTableField + "=" + c.RightTableName + "." + c.RightTableField + "   ");
                            }
                            else
                            {
                                errorLt.Add("表关联 表名"+ c.LeftTableName + "字段" + c.LeftTableField + "表名" + c.RightTableName+ "字段" + c.RightTableField+           "错误。  或条件不可为空。");



                            }




                        }

                        sb.Append(onConditionsLt.Join("  and "));

                    }




                }

            }
            #endregion
            #region where 条件
            if (model.Wheres != null && model.Wheres.Count > 0)
            {

                sb.Append("  where " + GetWhereString(model.Wheres));
            }

            #endregion

            #region  排序

            if (model.OrderCondition != null && model.OrderCondition.Count > 0)
            {
                sb.Append("  order by ");

                
                sbtemp.Length=0;
                foreach (var od in model.OrderCondition)
                {
                    sqlResult = ChinaToEnglist(od.OrderByType);
                    if (ValidatedTableName(od.TableName) && ValidatedColumnName(od.TableName, od.Fields) && sqlResult.IsOk)
                    {
                        sbtemp.Append("  " + od.TableName + "." + od.Fields + sqlResult.SqlString + ", ");
                    }
                    else
                    {
                        errorLt.Add("排序的表名"+ od.TableName + " 字段"+ od.Fields + " 或者排序"+ od.OrderByType + " 错误。");
                    }


                }
                string orderstring = sbtemp.ToString();
                sb.Append(orderstring.Substring(0, orderstring.LastIndexOf(',')));



            }


            #endregion


            return sb.ToString();

        }


        #region  工具函数


        /// <summary>
        /// 获取where条件的字符串
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        private static string GetWhereString(List<WhereCondition> model)
        {
            StringBuilder sb = new StringBuilder();

            if (model != null && model.Count > 0)
            {
                SqlResult sr = new SqlResult();

                foreach (var m in model)
                {

                    sr = ChinaToEnglist(m.LogicSymbol);
                    if (sr.IsOk) sb.Append(sr.SqlString);


                    if (m.ExtendWhere != null && m.ExtendWhere.Count > 0)
                    {
                        sb.Append(" (  ");
                        sb.Append(GetWhereString(m.ExtendWhere));
                        sb.Append(" ) ");
                    }
                    else
                    {


                        if (ValidatedTableName(m.LeftTableName)) sb.Append(m.LeftTableName);
                        sb.Append(".");
                        if (ValidatedColumnName(m.LeftTableName, m.LeftTableField)) sb.Append(m.LeftTableField);
                        sr = ChinaToEnglist(m.ConditionSymbol);
                        if (sr.IsOk) sb.Append(sr.SqlString);

                        if (string.IsNullOrEmpty(m.ConditionValue))
                        {
                            if (ValidatedTableName(m.RightTableName)) sb.Append(m.RightTableName);
                            sb.Append(".");
                            if (ValidatedColumnName(m.RightTableName, m.RightTableField)) sb.Append(m.RightTableField);
                        }
                        else
                        {
                            string[] conditionValueLt = m.ConditionValue.Split(',');
                            if (conditionValueLt != null && conditionValueLt.Length > 0)
                            {

                                if (m.ConditionSymbol == "集合")
                                {
                                    sb.Append(" (");

                                    string inValue = "";
                                    if (IsString(GetColumnType(m.LeftTableName, m.LeftTableField)))
                                    {
                                        foreach (string s in conditionValueLt)
                                        {
                                            if (IsCnEnNumberSymbol(s))
                                            {
                                                inValue += "'" + s + "',";

                                            }


                                        }


                                    }
                                    else
                                    {
                                        foreach (string s in conditionValueLt)
                                        {
                                            if (IsDouble(s))
                                            {
                                                inValue += s + ",";
                                            }

                                        }


                                    }



                                    sb.Append(inValue.Substring(0, inValue.LastIndexOf(',')) + " )");
                                }
                                else
                                {
                                    if (IsString(GetColumnType(m.LeftTableName, m.LeftTableField)))
                                    {
                                        sb.Append("'" + m.ConditionValue + "'");
                                    }
                                    else
                                    {
                                        sb.Append(m.ConditionValue);
                                    }



                                }


                            }

                        }

                    }

                }


            }




            return sb.ToString();
        }




        /// <summary>
        /// 符号转换
        /// </summary>
        /// <param name="input"></param>
        /// <returns></returns>
        private static SqlResult ChinaToEnglist(string input)
        {

            SqlResult result = new SqlResult();
            result.IsOk = true;
            switch (input.Trim())
            {
                case "集合":
                    result.SqlString = " in ";
                    break;

                case "和":
                    result.SqlString = " and ";
                    break;

                case "或":
                    result.SqlString = " or ";
                    break;

                case "等于":
                    result.SqlString = " = ";
                    break;
                case "不等于":
                    result.SqlString = "!=";
                    break;

                case "小于":
                    result.SqlString = "<";
                    break;

                case "大于":
                    result.SqlString = ">";
                    break;

                case "小于等于":
                    result.SqlString = "<=";
                    break;

                case "大于等于":
                    result.SqlString = ">=";
                    break;

                case "左连接":
                    result.SqlString = " left join ";
                    break;

                case "右连接":
                    result.SqlString = " right join ";
                    break;

                case "交叉连接":
                    result.SqlString = " cross  join ";
                    break;
                case "升序":
                    result.SqlString = " asc ";
                    break;
                case "降序":
                    result.SqlString = " desc ";
                    break;

                default:
                    result.Message = "关键字不存在或错误";
                    result.IsOk = false;
                    break;

            }



            return result;
        }


        public void SetTbAndColumnInfo()
        {

            if (tableColumnLt == null || tableColumnLt.Count > 0)
            {


                tableColumnLt = new List<TbAndColumnInfo>();

                tableColumnLt.Add(new TbAndColumnInfo("table1", "id", "int"));
                tableColumnLt.Add(new TbAndColumnInfo("table1", "name", "varchar"));
                tableColumnLt.Add(new TbAndColumnInfo("table1", "create", "datetime"));

                tableColumnLt.Add(new TbAndColumnInfo("table2", "userid", "int"));
                tableColumnLt.Add(new TbAndColumnInfo("table2", "username", "varchar"));
                tableColumnLt.Add(new TbAndColumnInfo("table2", "create", "datetime"));
            }



        }



        private static List<TbAndColumnInfo> tableColumnLt;
        /// <summary>
        /// 表名合法
        /// </summary>
        /// <param name="tableName"></param>
        /// <returns></returns>
        private static bool ValidatedTableName(string tableName)
        {

            return !string.IsNullOrEmpty(tableColumnLt.Find(t => t.TableName == tableName.Trim()).TableName);
        }
        /// <summary>
        /// 字段名合法
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="columnName"></param>
        /// <returns></returns>
        private static bool ValidatedColumnName(string tableName, string columnName)
        {
            return !string.IsNullOrEmpty(tableColumnLt.Find(t => t.TableName == tableName.Trim() && t.ColumnName == columnName.Trim()).TableName);
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="dbColumnType">数据表字段类型</param>
        /// <param name="dbType">数据库类型</param>
        /// <returns></returns>


        private static string GetColumnType(string tableName, string columnName)
        {
            return tableColumnLt.Find(t => t.TableName == tableName.Trim() && t.ColumnName == columnName.Trim()).ColumnType;
        }


        private static bool IsString(string dbColumnType, string dbType = "sqlserver")
        {
            bool result = false;


            switch (dbType)
            {

                case "sqlserver":
                    switch (dbColumnType.Trim())
                    {

                        case "varchar":
                        case "nvarchar":
                        case "sql_variant":
                        case "text":
                        case "char":
                        case "ntext":
                        case "nchar":
                        case "hierarchyid":
                        case "bit":
                        case "datetime":
                        case "time":
                        case "smalldatetime":
                        case "datetime2":
                        case "date":
                        case "uniqueidentifier":
                        case "datetimeoffset":
                            result = true;
                            break;

                        default:
                            break;


                    }
                    break;
                case "mysql":
                    switch (dbColumnType.Trim())
                    {

                        case "varchar":
                        case "text":
                        case "char":
                        case "enum":
                        case "mediumtext":
                        case "tinytext":
                        case "longtext":
                        case "bit":
                        case "datetime":
                        case "timestamp":
                        case "date":
                        case "time":
                        case "blob":
                            result = true;
                            break;

                        default:
                            break;


                    }
                    break;
                case "postgresql":
                    switch (dbColumnType.Trim())
                    {

                        case "boolean":
                        case "bool":
                        case "box":
                        case "bytea":
                        case "varchar":
                        case "character varying":
                        case "geometry":
                        case "name":
                        case "text":
                        case "char":
                        case "character":
                        case "cidr":
                        case "circle":
                        case "tsquery":
                        case "tsvector":
                        case "txid_snapshot":
                        case "uuid":
                        case "xml":
                        case "json":
                        case "timestamp":
                        case "timestamp with time zone":
                        case "timestamptz":
                        case "timestamp without time zone":
                        case "date":
                        case "time":
                        case "time with time zone":
                        case "timetz":
                        case "time without time zone":
                            result = true;
                            break;

                        default:
                            break;


                    }
                    break;
                default:
                    break;

            }


            return result;
        }

        #region  数字中文英文
        /// <summary>
        /// 数字中文英文
        /// </summary>
        /// <param name="input"></param>
        /// <returns></returns>
        private static bool IsCnEnNumber(string input)
        {
            Regex regex = new Regex(@"^[\u4E00-\u9FFFA-Za-z0-9]+$");
            return regex.IsMatch(input);
        }
        #endregion
        #region  数字中文英文
        /// <summary>
        /// 数字中文英文逗号 小数点
        /// </summary>
        /// <param name="input"></param>
        /// <returns></returns>
        private static bool IsCnEnNumberSymbol(string input)
        {
            Regex regex = new Regex(@"^[\u4E00-\u9FFFA-Za-z0-9.,]+$");
            return regex.IsMatch(input);
        }

        #endregion
        /// <summary>
        /// 是否是数字
        /// </summary>
        /// <param name="value"></param>
        /// <returns></returns>
        private static bool IsDouble(string input)
        {
            Regex regex = new Regex(@"^\d[.]?\d?$");
            return regex.IsMatch(input);

        }


        #endregion






    }
}
